﻿Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports HotelLibrary
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Xml.Serialization

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="TermProjectWS")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class HotelService
    Inherits System.Web.Services.WebService
    Dim db As New DBConnect
    Dim ds As New DataSet
    Dim objCommand As New SqlCommand
    Dim Hotel As New Hotel_t
    Dim vacay As New Vacation

    <WebMethod(), _
         XmlInclude(GetType(Hotel_t))>
    Public Function searchbyAmenities(array As ArrayList) As DataSet
        ds = New DataSet
        Dim i As Integer = Array.Count - 1
        Dim Smoking As String = "False"
        Dim AirCond As String = "False"
        Dim FreeBreakfast As String = "False"
        Dim Restuarant As String = "False"
        Dim Wifi As String = "False"
        Dim Business As String = "False"
        Dim Pet As String = "False"
        Dim Parking As String = "False"
        Dim Spa As String = "False"
        Dim Swimming As String = "False"
        For x As Integer = 0 To i
            Dim tempt As String = Array(x).ToString
            If array(x).ToString = "Smoking" Then
                Smoking = "True"
            Else
                If array(x).ToString = "AirCond" Then
                    AirCond = "True"
                Else
                    If array(x).ToString = "FreeBreakfast" Then
                        FreeBreakfast = "True"
                    Else
                        If array(x).ToString = "Restuarant" Then
                            Restuarant = "True"
                        Else
                            If array(x).ToString = "Wifi" Then
                                Wifi = "True"
                            Else
                                If array(x).ToString = "Business" Then
                                    Business = "True"
                                Else
                                    If array(x).ToString = "Pet" Then
                                        Pet = "True"
                                    Else
                                        If array(x).ToString = "Parking" Then
                                            Parking = "True"
                                        Else
                                            If array(x).ToString = "Spa" Then
                                                Spa = "True"
                                            Else
                                                If array(x).ToString = "Swimming" Then
                                                    Swimming = "True"
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        Next
        ds = db.getDataSet("Select * from ((Select * from Amenities_t where Smoking = '" & Smoking & "' and AirCond = '" & AirCond & "' and FreeBreakfast = '" & FreeBreakfast & "' and Restuarant = '" & Restuarant & "' and Wifi = '" & Wifi & "' and Business = '" & Business & "' and Pet = '" & Pet & "' and Parking = '" & Parking & "' and Spa = '" & Spa & "' and Swimming = '" & Swimming & "')as a inner join (Select * from Hotel_t) as b on a.HotelID = b.HotelID)")
        'objCommand.CommandType = CommandType.StoredProcedure
        'objCommand.CommandText = "searchbyAmenities"
        'objCommand.Parameters.AddWithValue("@Smoking", Smoking)
        'objCommand.Parameters.AddWithValue("@AirCond", AirCond)
        'objCommand.Parameters.AddWithValue("@FreeBreakfast", FreeBreakfast)
        'objCommand.Parameters.AddWithValue("@Restuarant", Restuarant)
        'objCommand.Parameters.AddWithValue("@Wifi", Wifi)
        'objCommand.Parameters.AddWithValue("@Business", Business)
        'objCommand.Parameters.AddWithValue("@Pet", Pet)
        'objCommand.Parameters.AddWithValue("@Parking", Parking)
        'objCommand.Parameters.AddWithValue("@Spa", Spa)
        'objCommand.Parameters.AddWithValue("@Swimming", Swimming)
        'ds = db.getDataSetUsingCmdObj(objCommand)
        Return ds

    End Function

    <WebMethod()> _
    Public Function insertRoom(mhd As Object, hotelID As String, Amenities As Integer) As Boolean
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.CommandText = "insertHotelDetails"
        objCommand.Parameters.AddWithValue("@RoomType", Amenities)
        objCommand.Parameters.AddWithValue("@HotelID", hotelID)
        objCommand.Parameters.AddWithValue("@NumberofRoom", mhd.NumberRoom)
        objCommand.Parameters.AddWithValue("@NightlyPrice", mhd.NightlyPrice)

        Dim result As Integer = db.doUpdateUsingCmdObj(objCommand)
        If result > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    <WebMethod(), _
        XmlInclude(GetType(Hotel_t))> _
    Public Function insertHotel(mHotel As Object) As Boolean
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.CommandText = "insertHotel"
        objCommand.Parameters.AddWithValue("@HotelName", mHotel.HotelName)
        objCommand.Parameters.AddWithValue("@City", mHotel.City)
        objCommand.Parameters.AddWithValue("@State", mHotel.State)
        objCommand.Parameters.AddWithValue("@ZipCode", mHotel.ZipCode)
        objCommand.Parameters.AddWithValue("@Star", mHotel.Stars)

        Dim result As Integer = db.doUpdateUsingCmdObj(objCommand)
        If result > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    <WebMethod()> _
    Public Function insertAmenities(mHotel As Object) As Boolean
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.CommandText = "insertAmenities_t"
        objCommand.Parameters.AddWithValue("@HotelID", CInt(mHotel.HotelID))
        objCommand.Parameters.AddWithValue("@Smoking", mHotel.Smoking)
        objCommand.Parameters.AddWithValue("@AirCond", mHotel.AirConditioner)
        objCommand.Parameters.AddWithValue("@FreeBreakfast", mHotel.FreeBreakfast)
        objCommand.Parameters.AddWithValue("@Restuarant", mHotel.Restaurant)
        objCommand.Parameters.AddWithValue("@Wifi", mHotel.Wifi)
        objCommand.Parameters.AddWithValue("@Business", mHotel.Business)
        objCommand.Parameters.AddWithValue("@Pet", mHotel.Pet)
        objCommand.Parameters.AddWithValue("@Parking", mHotel.Parking)
        objCommand.Parameters.AddWithValue("@Spa", mHotel.Spa)
        objCommand.Parameters.AddWithValue("@Swimming", mHotel.Swimming)

        Dim result As Integer = db.doUpdateUsingCmdObj(objCommand)
        If result > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    <WebMethod()> _
    Public Function getHotel(city As String, state As String) As DataSet
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.CommandText = "getHotel"
        objCommand.Parameters.AddWithValue("@City", city)
        objCommand.Parameters.AddWithValue("@State", state)
        ds = db.getDataSetUsingCmdObj(objCommand)
        Return ds

    End Function

    <WebMethod()> _
    Public Function getAmenities(hotelID As Integer) As DataSet
        ds = New DataSet
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.CommandText = "getAmenities"
        objCommand.Parameters.AddWithValue("@HotelID", hotelID)
        ds = db.getDataSetUsingCmdObj(objCommand)
        Return ds

    End Function

    <WebMethod()> _
    Public Function getAllAmenities() As DataSet
        ds = New DataSet
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.CommandText = "getAllAmenities"
        ds = db.getDataSetUsingCmdObj(objCommand)
        Return ds
    End Function

    <WebMethod()> _
    Public Function getRooml(hotelID As Integer) As DataSet
        ds = New DataSet
        objCommand.CommandType = CommandType.StoredProcedure
        objCommand.CommandText = "getRooml"
        objCommand.Parameters.AddWithValue("@HotelID", hotelID)
        ds = db.getDataSetUsingCmdObj(objCommand)
        Return ds
    End Function


    <WebMethod()> _
    Public Function ReserveRoom(thisRoom As HotelLibrary.TripDetail_t, thisCustomer As HotelLibrary.CustLib) As Boolean
        ' begin adding trip details into database from object
        Dim thisRoomID As Integer = thisRoom.RoomID
        Dim thisTripID As Integer = thisRoom.TripID
        Dim thisCustomerID As String = thisCustomer.LoginID
        Dim thisRoomType As String = thisRoom.RoomType
        Dim thisStartDate As Date = thisRoom.StartDate
        Dim thisEndDate As Date = thisRoom.EndDate

        ' determine the number of days stayed
        Dim numDaysStayed As Integer
        numDaysStayed = CInt(Math.Round(DateDiff("d", thisStartDate, thisEndDate)))

        Dim thisConnect As New DBConnect
        Dim commandText As String
        Dim dbResult As Integer

        commandText = "INSERT INTO TripDetail_t (TripID, LoginID, RoomID, RoomType, StartDate, EndDate, NumDays) VALUES (@TripID, @LoginID, @RoomID, @RoomType, @StartDate, @EndDate, @NumDays);"
        Dim thisCommand As New System.Data.SqlClient.SqlCommand(commandText)

        thisCommand.Parameters.Add("@TripID", SqlDbType.Int)
        thisCommand.Parameters("@TripID").Value = thisTripID
        thisCommand.Parameters.Add("@LoginID", SqlDbType.VarChar)
        thisCommand.Parameters("@LoginID").Value = thisCustomerID
        thisCommand.Parameters.Add("@RoomID", SqlDbType.Int)
        thisCommand.Parameters("@RoomID").Value = thisRoomID
        thisCommand.Parameters.Add("@RoomType", SqlDbType.VarChar)
        thisCommand.Parameters("@RoomType").Value = thisRoomType
        thisCommand.Parameters.Add("@StartDate", SqlDbType.Date)
        thisCommand.Parameters("@StartDate").Value = thisStartDate
        thisCommand.Parameters.Add("@EndDate", SqlDbType.Date)
        thisCommand.Parameters("@EndDate").Value = thisEndDate
        thisCommand.Parameters.Add("@NumDays", SqlDbType.Int)
        thisCommand.Parameters("@NumDays").Value = numDaysStayed

        dbResult = thisConnect.doUpdateUsingCmdObj(thisCommand)

        If dbResult <= 0 Then
            Return False
        Else
            ' *** DATABASE CHANGES HAVE BEEN MADE SINCE WRITING, CODE KEPT BEHIND ONLY FOR LEGACY PURPOSES ****
            '' begin update reservation string for individual room
            'Dim currentReservationString As String
            'Dim reservationStringAddition As String
            '' add ! at beginning of each string and ? at the end to make it easier to read them later
            'reservationStringAddition = "!" & thisStartDate.ToString & "-" & thisEndDate.ToString & "?"

            'currentReservationString = GetRoomAvailability(thisRoom)
            'If currentReservationString = "-1" Or "" Then
            '    currentReservationString = reservationStringAddition
            'Else
            '    currentReservationString = currentReservationString + reservationStringAddition
            'End If

            'Dim anotherConnect As New DBConnect
            'Dim aCommandString As String
            'Dim rowsAffected As Integer

            'aCommandString = "UPDATE Hotel_Detail_t SET DateRoomsReserved=@ReserveString WHERE Id=@RoomID;"
            'Dim aCommand As New System.Data.SqlClient.SqlCommand(aCommandString)
            'aCommand.Parameters.Add("@ReserveString", SqlDbType.VarChar)
            'aCommand.Parameters("@ReserveString").Value = currentReservationString
            'aCommand.Parameters.Add("@RoomID", SqlDbType.Int)
            'aCommand.Parameters("@RoomID").Value = thisRoomID

            'rowsAffected = anotherConnect.doUpdateUsingCmdObj(aCommand)

            'If rowsAffected <= 0 Then
            '    Return False
            'Else

            ' calculate and record price for Customer
            Dim thisNightlyPrice As Double = GetNightlyPrice(thisRoom)
            Dim totalPrice As Double

            totalPrice = numDaysStayed * thisNightlyPrice

            Dim currentTotal As Double = GetCustomerTotalSales(thisCustomer)
            If currentTotal = "-1" Then
                currentTotal = totalPrice
            Else
                currentTotal = currentTotal + totalPrice
            End If

            Dim finalConnect As New DBConnect
            Dim finalCommandString As String
            Dim rowsChanged As Integer

            finalCommandString = "UPDATE Customer_t SET TotalDollarSales=@TotalSales WHERE LoginID=@LoginID;"
            Dim finalCommand As New System.Data.SqlClient.SqlCommand(finalCommandString)
            finalCommand.Parameters.Add("@TotalSales", SqlDbType.Float)
            finalCommand.Parameters("@TotalSales").Value = currentTotal
            finalCommand.Parameters.Add("@LoginID", SqlDbType.VarChar)
            finalCommand.Parameters("@LoginID").Value = thisCustomer.LoginID

            rowsChanged = finalConnect.doUpdateUsingCmdObj(finalCommand)

            If rowsChanged <= 0 Then
                Return False
            Else
                ' update hotel rooms "rooms remaining" quantity
                Dim roomsLeft As Integer = GetRoomsRemaining(thisRoomID)
                If roomsLeft = 0 Then
                    Return False
                Else
                    roomsLeft = roomsLeft - 1
                    Dim roomConnect As New DBConnect
                    Dim roomCommandString As String
                    Dim roomRowsChanged As Integer

                    roomCommandString = "UPDATE Room SET NumberofRoom=@NumberRoom WHERE RoomID=@RoomID;"
                    Dim roomCommand As New System.Data.SqlClient.SqlCommand(roomCommandString)
                    roomCommand.Parameters.Add("@NumberRoom", SqlDbType.Int)
                    roomCommand.Parameters("@NumberRoom").Value = roomsLeft
                    roomCommand.Parameters.Add("@RoomID", SqlDbType.Int)
                    roomCommand.Parameters("@RoomID").Value = thisRoomID

                    roomRowsChanged = finalConnect.doUpdateUsingCmdObj(roomCommand)

                    If roomRowsChanged <= 0 Then
                        Return False
                    Else
                        ' everything was completed successfully!
                        Return True
                    End If
                End If
            End If
        End If
    End Function

    <WebMethod()> _
    Public Function GetRoomAvailability(thisRoom As HotelLibrary.TripDetail_t) As String
        Dim thisRoomID As Integer = thisRoom.RoomID
        Dim thisConnect As New DBConnect
        Dim commandString As String
        Dim result As String

        commandString = "SELECT DatesRoomReserved FROM Hotel_Detail_t WHERE Id=@RoomID;"
        Dim thisCommand As New System.Data.SqlClient.SqlCommand(commandString)
        thisCommand.Parameters.Add("@RoomID", SqlDbType.Int)
        thisCommand.Parameters("@RoomID").Value = thisRoomID

        result = thisConnect.ExecScalarFunction(thisCommand)

        If result Is Nothing Or result = "" Then
            Return "-1"
        Else
            Return result
        End If
    End Function

    <WebMethod()> _
    Public Function GetNightlyPrice(thisRoom As HotelLibrary.TripDetail_t) As Double
        Dim thisRoomID As Integer = thisRoom.RoomID
        Dim thisConnect As New DBConnect
        Dim commandString As String
        Dim result As String

        commandString = "SELECT NightlyPrice FROM Hotel_Detail_t WHERE Id=@RoomID;"
        Dim thisCommand As New System.Data.SqlClient.SqlCommand(commandString)
        thisCommand.Parameters.Add("@RoomID", SqlDbType.Int)
        thisCommand.Parameters("@RoomID").Value = thisRoomID

        result = thisConnect.ExecScalarFunction(thisCommand)

        If result Is Nothing Or result = "" Then
            Return "-1"
        Else
            Return result
        End If
    End Function

    <WebMethod()> _
    Public Function GetCustomerTotalSales(thisCustomer As HotelLibrary.CustLib) As Double
        Dim thisCustomerID As String = thisCustomer.LoginID
        Dim thisConnect As New DBConnect
        Dim commandString As String
        Dim result As String

        commandString = "SELECT TotalDollarSales FROM Customer_t WHERE LoginID=@CustomerID;"
        Dim thisCommand As New System.Data.SqlClient.SqlCommand(commandString)
        thisCommand.Parameters.Add("@CustomerID", SqlDbType.VarChar)
        thisCommand.Parameters("@CustomerID").Value = thisCustomerID

        result = thisConnect.ExecScalarFunction(thisCommand)

        If result Is Nothing Or result = "" Then
            Return "-1"
        Else
            Return result
        End If
    End Function

    <WebMethod()> _
    Public Function GetRoomsRemaining(RoomID As Integer) As Integer
        Dim thisConnect As New DBConnect
        Dim commandString As String
        Dim result As String

        commandString = "SELECT NumberofRoom FROM Room WHERE RoomID=@RoomID;"
        Dim thisCommand As New System.Data.SqlClient.SqlCommand(commandString)
        thisCommand.Parameters.Add("@RoomID", SqlDbType.Int)
        thisCommand.Parameters("@RoomID").Value = RoomID

        result = thisConnect.ExecScalarFunction(thisCommand)

        If result Is Nothing Or result = "" Then
            Return "-1"
        Else
            Return result
        End If
    End Function

    <WebMethod()> _
    Public Function insertCart(vacay As Vacation, LoginID As String) As Boolean
        Dim i As Integer = 0


        i = db.doUpdate("Insert into Cart_t (Type, Quantity, Price, LoginID) Values('" & vacay.Type & "'," & vacay.Quantity & "," & vacay.Price & ",'" & vacay.LoginID & "')")
        If (i > 0) Then
            Return True
        Else
            Return False
        End If

    End Function

    <WebMethod()> _
    Public Function getCart(Login As String) As DataSet
        ds = New DataSet
        ds = db.getDataSet("Select * from Cart_t where LoginID = '" & Login & "'")

        Return ds
    End Function

    <WebMethod()> _
    Public Function cleanCart() As Boolean
        If (db.doUpdate("Delete * from Cart_t") > 0) Then
            Return True
        Else
            Return False
        End If

    End Function
End Class